﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using myomscl.Utils;

namespace myomscl.DataObjects
{
    public class MaterialsViewDAL : SqlBaseDal
    {
        public EstateDataSet.MaterialsDataTable GetMaterialsListByMunicipality(WorkflowFilterSql filterSql)
        {
            var result = new EstateDataSet.MaterialsDataTable();

            using (SqlConnection dbc = GetConnection())
            {
                try
                {
                    var cmd = dbc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = Constants.Sp_Name_Materials_GetListForMunicipality;

                    var parameters = new SqlParameter[1];
                    parameters[0] = filterSql.MunicipalityId;

                    cmd.Parameters.AddRange(parameters);

                    var da = new SqlDataAdapter(cmd);
                    dbc.Open();
                    da.Fill(result);
                }
                catch (SqlException ex)
                {
                    //Logging exception
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
            return result;
        }

        public EstateDataSet.MaterialsDataTable GetMaterialsDetails(WorkflowFilterSql filterSql)
        {
            var result = new EstateDataSet.MaterialsDataTable();

            using (SqlConnection dbc = GetConnection())
            {
                try
                {
                    var cmd = dbc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = Constants.Sp_Name_Materials_GetDetails;

                    var parameters = new SqlParameter[1];
                    parameters[0] = filterSql.MaterialsId;

                    cmd.Parameters.AddRange(parameters);

                    var da = new SqlDataAdapter(cmd);
                    dbc.Open();
                    da.Fill(result);
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
            return result;
        }

        public bool UpdateMaterial(WorkflowFilterSql filterSql)
        {
            using (SqlConnection dbc = GetConnection())
            {
                try
                {
                    var cmd = dbc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = Constants.Sp_Name_Materials_Update;

                    var parameters = new SqlParameter[8];
                    parameters[0] = filterSql.MunicipalityId;
                    parameters[1] = filterSql.MaterialsId;
                    parameters[2] = filterSql.Name;
                    parameters[3] = filterSql.Unit;
                    parameters[4] = filterSql.Price;
                    parameters[5] = filterSql.Disabled;
                    parameters[6] = filterSql.ConstructorPrice;
                    parameters[7] = new SqlParameter()
                    {
                        Direction = ParameterDirection.ReturnValue,
                        SqlDbType = SqlDbType.Bit
                    };

                    cmd.Parameters.AddRange(parameters);
                    if (dbc.State != ConnectionState.Open)
                        dbc.Open();

                    cmd.ExecuteNonQuery();
                    return cmd.Parameters[7].Value.ToString() != "1";
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
        }

        public bool CreateMaterial(WorkflowFilterSql filterSql)
        {
            using (SqlConnection dbc = GetConnection())
            {
                try
                {
                    var cmd = dbc.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = Constants.Sp_Name_Materials_Create;

                    var parameters = new SqlParameter[7];
                    parameters[0] = filterSql.MunicipalityId;
                    parameters[1] = filterSql.Name;
                    parameters[2] = filterSql.Price;
                    parameters[3] = filterSql.Unit;
                    parameters[4] = filterSql.Disabled;
                    parameters[5] = filterSql.ConstructorPrice;
                    parameters[6] = new SqlParameter()
                    {
                        Direction = ParameterDirection.ReturnValue,
                        SqlDbType = SqlDbType.Bit
                    };

                    cmd.Parameters.AddRange(parameters);
                    if (dbc.State != ConnectionState.Open)
                        dbc.Open();

                    cmd.ExecuteNonQuery();
                    return cmd.Parameters[6].Value.ToString() != "1";
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    dbc.Close();
                }
            }
        }

    }
}
